<%-- 
    Document   : monthly
    Created on : Dec 7, 2011, 3:30:30 AM
    Author     : Michael
--%>

<%@page import="java.sql.ResultSet"%>
<%@page import="java.util.Calendar"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Monthly Sales Report</title>
    </head>
    <body>
        <input type="button" value="Home" onclick="window.location='/TheLoop/gohome.jsp'"/>&nbsp;
          <input type="button" value="Log Out" onclick="window.location='/TheLoop/logout.jsp'"/>
        <h1>Sales by Month</h1>
        <table width="400">
            <tr>
                <td>Month</td>
                <td>Income</td>
            </tr>
        <%
            try
            {
                Connection conn = new DBClasses.GetConnection().getConnection();
                String[] months = new String[]{"Jan", "Feb", "March", "April", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec"};
                for(int i=0;i<12;i++)
                {
                    ResultSet rs = conn.createStatement().executeQuery("SELECT SUM(NumUnits*UnitPrice) " + 
                    "FROM advertisement a, sales s " + 
                    "WHERE s.AdID=a.AdvertisementID  " + 
                    "AND EXTRACT(MONTH FROM s.DATE)=" + (i+1));
                    if(rs.next())
                    {
                        double price = rs.getDouble(1);

                    %>
                    <tr>
                        <td><%= months[i] %></td>
                        <td>$<%= price %></td>
                    </tr>


                    <%
                    }
                }
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        %>
                </table>
    </body>
</html>
